﻿using Microsoft.Reporting.WinForms;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;
using System.IO;
using ReportSystem_XY.Models;
using System.Configuration;
using ReportSystem_XY.Utility;
using System.Threading;
using System.Net.NetworkInformation;
using System.Drawing.Drawing2D;
using System.Linq;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.StartPanel;
using ReportSystem_XY.SIP;

namespace ReportSystem_XY
{
    public partial class FrmMain : Form
    {

        public FrmMain()
        {
            InitializeComponent();
        }
        String Source = "";
        String SQLTime = "";
        String dbname = "";
        List<string> strList = new List<string>();
        private string appPath = Application.StartupPath;
        private void Form1_Load(object sender, EventArgs e)
        {
            // strList = XmlHelper.ReadXml("switchbit");
            dbname = XmlHelper.ReadXml("database")[0];
            btn_ReportSearch.Enabled = false;
            this.ReportViewer1.RefreshReport();
            tabControl.ItemSize = new Size(0, 1);
            tabControl.Appearance = TabAppearance.Buttons;
            tabControl.SizeMode = TabSizeMode.Fixed;
            textBox2.Text = "";
            textBox3.Text = "";
            textBox5.Text = "";

            ReportViewer1.Visible = false;
            DT_EndDate.MaxDate = DateTime.Today;
            DT_BeginDate.MaxDate = DT_EndDate.Value;
            DT_BeginTime.Value = Convert.ToDateTime("00:00:00");
            DT_EndTime.Value = DateTime.Now;
            ToolStripStatusLabel3.Text = "";
            ToolStripStatusLabel1.Text = DateTime.Now.ToString();

            //报表查询类型选择
            cmb_ReportType.DataSource = XmlHelper.ReadXml("reporttype");
            //采样时间初始化
            cmb_SampleTime.DataSource = XmlHelper.ReadXml("sampletime");
            cmb_SampleTime.SelectedIndex = 6;
            cmb_Device.DataSource = XmlHelper.ReadXml("device");
            // this.TopMost = true; 
        }
        #region 菜单栏
        private void Timer1_Tick(object sender, EventArgs e)
        {
            ToolStripStatusLabel1.Text = DateTime.Now.ToString();
        }

        private void DateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            if (DT_EndDate.Value < DT_BeginDate.Value)
            {
                DT_EndDate.Value = DT_BeginDate.Value;
            }
        }
        private void DateTimePicker2_ValueChanged(object sender, EventArgs e)
        {
            DT_BeginDate.MaxDate = DT_EndDate.Value;
        }

        private void 退出XToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void panel1_Paint(object sender, PaintEventArgs e)
        {

        }
        private void DateTimePicker3_ValueChanged(object sender, EventArgs e)
        {
            if (DT_EndTime.Value < DT_BeginTime.Value)
            {
                DT_EndTime.Value = DT_BeginTime.Value;
            }
        }

        private void DateTimePicker4_ValueChanged(object sender, EventArgs e)
        {
            DT_BeginTime.MaxDate = DT_EndTime.Value;
        }

        private void ComboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            btn_ReportSearch.Enabled = false;
        }

        private void toolStripMenuItem1_Click(object sender, EventArgs e)
        {

            btn_ReportSearch.Enabled = false;
            pictureBox3.Show();
            ReportViewer1.Hide();
            tabPage2.Hide();
            tabPage1.Show();
        }

        private void 数据库备份还原ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            textBox6.Text = "";
            listBox1.Items.Clear();
            tabPage1.Hide();
            tabPage2.Show();
        }
        String BackupsFile = "";
        String RestoreFile = "";


        public object Product_InfoTableAdapter { get; private set; }

        private void button3_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog TargetAdd = new FolderBrowserDialog();
            TargetAdd.Description = "请选择数据库备份的目标位置";
            TargetAdd.RootFolder = Environment.SpecialFolder.MyComputer;
            TargetAdd.ShowNewFolderButton = false;
            if (TargetAdd.ShowDialog() == DialogResult.OK)
            {
                textBox3.Text = TargetAdd.SelectedPath;
                BackupsFile = textBox3.Text + "\\" + textBox2.Text + "_" + DateTime.Now.ToString("yyyyMMddhhmm") + ".bak";
                textBox4.Text = BackupsFile;
            }
        }
        private void textBox2_TextChanged(object sender, EventArgs e)
        {
            BackupsFile = textBox3.Text + "\\" + textBox2.Text + "_" + DateTime.Now.ToString("yyyyMMddhhmm") + ".bak";
            textBox4.Text = BackupsFile;
        }
        private void button4_Click(object sender, EventArgs e)
        {
            textBox4.Text = BackupsFile;
            if (textBox2.Text == "" || textBox3.Text == "")
            {
                MessageBox.Show("请填写正确的备份路径及文件名！", "提示框");
            }
            else
            {
                if (!File.Exists(BackupsFile))
                {

                    String Sql = "backup database " + dbname + " to disk='" + BackupsFile + "' with Init";
                    SQLHelper.ExecuteNonQuery(Sql);
                    MessageBox.Show("你已经成功备份当前数据！", "系统消息");
                }
                else
                {
                    MessageBox.Show("备份失败，该文件名已存在！", "提示框");
                }
            }
        }

        private void button7_Click(object sender, EventArgs e)
        {
            /*
            OpenFileDialog BackupSource = new OpenFileDialog();
            BackupSource.Multiselect = false;
            BackupSource.InitialDirectory = Environment.SpecialFolder.MyDocuments.ToString();
            BackupSource.Filter="备份文件(*.bak)|*.bak";
            if (BackupSource.ShowDialog() == DialogResult.OK)
            {
                RestoreFile=BackupSource.FileName;
                textBox5.Text = BackupSource.FileName;
            }
            */
            FolderBrowserDialog TargetAdd = new FolderBrowserDialog();
            TargetAdd.Description = "请选择数据库备份所在的目录";
            TargetAdd.RootFolder = Environment.SpecialFolder.MyComputer;
            TargetAdd.ShowNewFolderButton = false;
            if (TargetAdd.ShowDialog() == DialogResult.OK)
            {
                listBox1.Items.Clear();
                listBox1.SelectedItem = null;
                textBox6.Text = "";
                textBox5.Text = TargetAdd.SelectedPath;
                String[] Files = Directory.GetFiles(textBox5.Text);
                foreach (string str in Files)
                    if (str.Substring(str.LastIndexOf('.') + 1).ToLower() == "bak")
                        listBox1.Items.Add(str);
            }
        }
        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listBox1.SelectedItem != null)
            {
                RestoreFile = listBox1.SelectedItem.ToString();
                textBox6.Text = RestoreFile;
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            if (textBox5.Text == "" || textBox6.Text == "")
            {
                MessageBox.Show("请选择正确的还原路径及文件名！", "提示框");
            }
            else
            {
                if (File.Exists(RestoreFile))
                {

                    String Sql = ("use master " +
                    "alter database " + dbname + " set offline with rollback immediate " +
                    "restore database " + dbname + " from disk = '" + RestoreFile + "' with Replace " +
                    "alter database " + dbname + " set online with rollback immediate");
                    SQLHelper.ExecuteNonQuery(Sql);
                    MessageBox.Show("你已经成功还原选定的数据！", "系统消息");

                }
                else
                {
                    MessageBox.Show("还原失败，该路径文件名不存在！", "提示框");
                }
            }
        }

        #endregion

        private int reportType;
        /// <summary>
        /// 根据报表类型查询批号，分CIP,SIP两类
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_BatchSearch_Click(object sender, EventArgs e)
        {
            //
            string deviceName = cmb_Device.SelectedItem.ToString();
            string selectType = cmb_ReportType.SelectedItem.ToString();
            SQLTime = " where (logtime between '" + DT_BeginDate.Value.ToString("yyyy-MM-dd") + " " + DT_BeginTime.Value.ToString("HH:mm:ss") + "' And '" + DT_EndDate.Value.ToString("yyyy-MM-dd") + " " + DT_EndTime.Value.ToString("HH:mm:ss") + "')";
            string Sql = "";

            //1、判断是稀配OR浓配
            if (deviceName == "630L配置系统")
            {
                //处理浓配的报表
                //查找SIP的报表清单
                if (selectType == "灭菌")
                {
                    reportType = 1;
                    Sql = "SELECT DISTINCT REPORTNAME, USERNAME, tankid, TEMPSP, TIMESP, PTSP FROM TBLSTEPINFO where reportname IN (select DISTINCT reportname from(SELECT DISTINCT reportname, max(stepno) as stepno FROM tblStepInfo "
                        + SQLTime + "and reporttype =" + reportType + " group by reportname) as t where t.stepno = 60)";
                    GetBatchNameBasesListAll<BatchNameBases>(Sql);
                    return;
                }
                //查找CIP的报表清单
                else if (selectType == "清洗")
                {
                    reportType = 2;
                    Sql = "SELECT DISTINCT REPORTNAME, USERNAME, tankid, TEMPSP, TIMESP, PTSP FROM TBLSTEPINFO where reportname IN (select DISTINCT reportname from(SELECT DISTINCT reportname, max(stepno) as stepno FROM tblStepInfo "
                         + SQLTime + "and reporttype =" + reportType + " group by reportname) as t where t.stepno >=100)";
                    GetBatchNameBasesListAll<BatchNameBases>(Sql);
                    return;
                }
                ////查找PRO的报表清单
                else if (selectType == "生产")
                {
                    Source = "tblProData";
                    SQLTime = " where (starttime between '" + DT_BeginDate.Value.ToString("yyyy-MM-dd") + " " + DT_BeginTime.Value.ToString("HH:mm:ss") + "' And '" + DT_EndDate.Value.ToString("yyyy-MM-dd") + " " + DT_EndTime.Value.ToString("HH:mm:ss") + "')";

                    Sql = "SELECT DISTINCT  * FROM " + Source + " " + SQLTime + "  AND tankid = 4  order by reportname";
                    reportType = 3;
                    GetBatchNameBasesListAll<BatchNameBasesProd>(Sql);
                    reportType = 3;
                    return;
                }
                else
                {
                    reportType = 0;
                    return;
                }

            }
            //稀配线的报表
            else
            {
                if (selectType == "灭菌")
                {
                    Source = "tblSIPData";
                    reportType = 1;
                    Sql = "SELECT DISTINCT reportname,username,tankid,timesp,tempsp,ptsp FROM " + Source + " " + SQLTime + " order by reportname";
                    GetBatchNameBasesListAll<BatchNameBases>(Sql);
                    return;
                }
                else if (selectType == "清洗")
                {
                    Source = "tblCIPData";
                    reportType = 2;
                    Sql = "SELECT DISTINCT reportname,username,tankid FROM " + Source + " " + SQLTime + " order by reportname";
                    GetBatchNameBasesListAll<BatchNameBasesCIP>(Sql);
                    return;
                }
                else if (selectType == "生产")
                {
                    Source = "tblProData";
                    SQLTime = " where (starttime between '" + DT_BeginDate.Value.ToString("yyyy-MM-dd") + " " + DT_BeginTime.Value.ToString("HH:mm:ss") + "' And '" + DT_EndDate.Value.ToString("yyyy-MM-dd") + " " + DT_EndTime.Value.ToString("HH:mm:ss") + "')";

                    Sql = "SELECT DISTINCT  * FROM " + Source + " " + SQLTime + "  AND tankid in (1,2)  order by reportname";
                    reportType = 3;
                    GetBatchNameBasesListAll<BatchNameBasesProd>(Sql);
                    return;
                }
                else
                {
                    Source = "";
                    reportType = 0;
                    return;
                }
            }
        }


        /// <summary>
        /// 获取批号
        /// </summary>
        /// <param name="Sql"></param>
        /// 
        private void GetBatchNameBasesListAll<T>(string sql)
        {
            List<T> batchNameBases = new List<T>();
            DataTable dt = SQLHelper.ExecuteDataTable(sql);
            foreach (DataRow dr in dt.Rows)
            {
                batchNameBases.Add(dr.DataRowToModel<T>());//反射
            }
            cmb_Batch.DataSource = batchNameBases;
            cmb_Batch.DisplayMember = "reportname";
            if (batchNameBases.Count > 0)
            {
                btn_ReportSearch.Enabled = true;
            }
            else
            {
                MessageBox.Show("没有查到批号信息");
            }
        }

        /// <summary>
        /// 根据前面查到的批号，查询报表的具体数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_ReportSearch_Click(object sender, EventArgs e)
        {

            List<ReportParameter> list = new List<ReportParameter>();
            list.Clear();
            int tankid;

            string SampleTime = cmb_SampleTime.SelectedItem.ToString();

            //判断report的类型
            if (reportType == 1)// SIP报表
            {
                BatchNameBases batchNameBase = (BatchNameBases)cmb_Batch.SelectedItem;
                tankid = (int)batchNameBase.tankid;
                if (tankid < 4 && tankid > 0)//稀配SIP1,2,3
                {
                    DataTable dt_data = new DataTable();
                    dt_data = SQLHelper.ExecuteDataTable(SIPTools.GetCmdTextString(Source, "1", batchNameBase.reportname));//不同的tank，报表名称不一样，通过报表名称也可以查到不同的罐
                    string sipStart = dt_data.Compute("MIN(logtime)", "true").ToString();
                    string sipStop = dt_data.Compute("MAX(logtime)", "true").ToString();

                    //获取灭菌步的开始和结束时间，判断时间间隔是否达标
                    string sterStart = dt_data.Compute("MIN(logtime)", "batchstep=1 and processStep=7").ToString();
                    string sterStop = dt_data.Compute("MAX(logtime)", "batchstep=1 and processStep=7").ToString();
                    if (sterStart == "" || sterStop == "")
                    {
                        MessageBox.Show("没查询到相应报表数据！");
                        return;
                    }
                    int spancount = (int)Convert.ToDateTime(sterStop).Subtract(Convert.ToDateTime(sterStart)).TotalSeconds;
                    if (spancount < 1190)
                    {
                        MessageBox.Show("灭菌没有完成！");
                        return;
                    }
                    //计算开始和结束的时间间隔

                    //SIP时间调整
                    //if (Convert.ToInt32(strList[1]) == 1)
                    if (XmlHelper.ReadXml("switchbit", "SIPTimeCheck") == "1")
                    {
                        int tsSP = (int)batchNameBase.timesp;
                        int ts = (int)Convert.ToDateTime(sterStop).Subtract(Convert.ToDateTime(sterStart)).TotalSeconds;
                        if (ts != tsSP)
                        {
                            if (ts < tsSP)
                            {
                                //计算准确的开始时间
                                string rightStart = (Convert.ToDateTime(sterStop).AddSeconds(-1 * tsSP)).ToString("yyyy-MM-dd HH:mm:ss");
                                DataRow[] dr = dt_data.Select("1=1", "logtime asc");
                                int span = tsSP - ts;
                                for (int j = 0; j < span; j++)
                                {
                                    string strDate = (Convert.ToDateTime(rightStart).AddSeconds(j)).ToString("yyyy-MM-dd HH:mm:ss");
                                    for (int i = 0; i < dr.Length; i++)
                                    {
                                        if (Convert.ToDateTime(dr[i]["logtime"].ToString()).ToString("yyyy-MM-dd HH:mm:ss") == strDate)
                                        {
                                            string sqlA = "exec usp_testA '" + strDate + "'";
                                            SQLHelper.ExecuteNonQuery(sqlA);

                                        }
                                    }
                                }
                                Thread.Sleep(1000);
                            }
                            else if (ts > tsSP)
                            {
                                string rightStart = (Convert.ToDateTime(sterStop).AddSeconds(-1 * tsSP)).ToString("yyyy-MM-dd HH:mm:ss");
                                DataRow[] dr = dt_data.Select("processstep=6 or processstep=7", "logtime asc");
                                int span = ts - tsSP;
                                for (int j = 0; j < span; j++)
                                {
                                    string strDate = (Convert.ToDateTime(rightStart).AddSeconds(-1 * (j + 1))).ToString("yyyy-MM-dd HH:mm:ss");
                                    for (int i = 0; i < dr.Length; i++)
                                    {
                                        string dddd = Convert.ToDateTime(dr[i]["logtime"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                                        if (Convert.ToDateTime(dr[i]["logtime"].ToString()).ToString("yyyy-MM-dd HH:mm:ss") == strDate)
                                        {
                                            string sqlA = "exec usp_testB '" + strDate + "'";
                                            SQLHelper.ExecuteNonQuery(sqlA);

                                        }
                                    }
                                }
                                Thread.Sleep(1000);
                            }
                        }
                    }


                    //获取灭菌步骤信息
                    DataTable dt_step = new DataTable();
                    dt_step = SIPTools.GetSIPStepInfos(Source, batchNameBase.reportname);
                    //步序时间调整
                    if (XmlHelper.ReadXml("switchbit", "StepCheck") == "1")
                    {
                        SIPTools.CalSIPStep(dt_step);
                        dt_step = SIPTools.GetSIPStepInfos(Source, batchNameBase.reportname);
                    }

                    //查找带时间间隔的数据 使用tankid 来区分不同的罐
                    dt_data = SQLHelper.ExecuteDataTable(SIPTools.GetCmdTextString(Source, SampleTime, batchNameBase.reportname));
                    //更新灭菌步骤的开始和停止时间
                    sterStart = dt_data.Compute("Min(logtime)", "batchstep=1 and processStep=7").ToString();
                    sterStop = dt_data.Compute("Max(logtime)", "batchstep=1 and processStep=7").ToString();

                    // DataTable dataTrend=  Get2000LTTData(loc2utcstr(sipStart), loc2utcstr(sipStop), "1");
                    //DataTable dataSter = Get2000LTTData(loc2utcstr(sterStart), loc2utcstr(Convert.ToDateTime(sterStop).AddSeconds(1).ToString()), SampleTime);


                    spancount = (int)Convert.ToDateTime(sterStop).Subtract(Convert.ToDateTime(sterStart)).TotalSeconds;
                    if (spancount < 1200)
                    {
                        MessageBox.Show("灭菌没有完成！");
                        return;
                    }
                    //查询出灭菌步的开始时间和结束时间以及采集的数据总数
                    DataRow[] array = dt_data.Select("batchstep=1 and processStep=7");
                    //把DataRow的数据转成DataTable 这个数据显示给灭菌段温度信息 表格
                    DataTable dt_Ster = SQLHelper.ExecuteDataTable(SIPTools.GetCmdTextString(Source, SampleTime, batchNameBase.reportname, sterStart, sterStop));//扩展方法
                    Double tt2102d = Convert.ToDouble(dt_data.Compute("Min(TT2102)", "batchstep=1 and processStep=7"));


                    //这两个索引值是用来给趋势图做划线用的，趋势图里面的两条竖线
                    int startIndex = (int)Convert.ToDateTime(sterStart).Subtract(Convert.ToDateTime(sipStart)).TotalSeconds - 2;
                    int stopIndex = startIndex + 1200;


                    //MessageBox.Show("数据查询完成");
                    ToolStripStatusLabel3.Text = "数据查询完成";
                    string[] startdate = sipStart.Split(' ');

                    //往报表里面传递参数        
                    list.Add(new ReportParameter("BatchString", batchNameBase.reportname));
                    list.Add(new ReportParameter("ReportType", "SIP"));
                    list.Add(new ReportParameter("Par0", startIndex.ToString()));//灭菌开始的索引
                    list.Add(new ReportParameter("Par1", stopIndex.ToString()));//灭菌结束的索引
                    list.Add(new ReportParameter("Par2", Convert.ToDateTime(sterStart).ToString("yyyy-MM-dd HH:mm:ss")));//灭菌开始的时间
                    list.Add(new ReportParameter("Par3", Convert.ToDateTime(sterStop).ToString("yyyy-MM-dd HH:mm:ss")));//灭菌结束的时间
                    list.Add(new ReportParameter("Temp", ((BatchNameBases)cmb_Batch.SelectedItem).tempsp.ToString()));//灭菌温度
                    list.Add(new ReportParameter("PT", ((BatchNameBases)cmb_Batch.SelectedItem).ptsp.ToString()));//灭菌压力
                    list.Add(new ReportParameter("time", ((BatchNameBases)cmb_Batch.SelectedItem).timesp.ToString()));//灭菌时间

                    list.Add(new ReportParameter("Operator", batchNameBase.username));//操作名称
                    list.Add(new ReportParameter("Date", Convert.ToDateTime(startdate[0]).ToString("yyyy-MM-dd")));//灭菌日期
                    list.Add(new ReportParameter("Pingming", "AAA"));//品名
                    list.Add(new ReportParameter("Guige", "500L"));//规格
                    list.Add(new ReportParameter("StartTime", Convert.ToDateTime(sipStart).ToString("yyyy-MM-dd HH:mm:ss")));//SIP开始时间
                    list.Add(new ReportParameter("StopTime", Convert.ToDateTime(sipStop).ToString("yyyy-MM-dd HH:mm:ss")));//SIP结束时间
                    list.Add(new ReportParameter("DetailDisplay", (this.checkBox1.Checked == true) ? "true" : "false"));

                    ReportViewer1.Visible = true;

                    ReportViewer1.ProcessingMode = ProcessingMode.Local;
                    ReportViewer1.LocalReport.DataSources.Clear();
                    if (tankid == 1)//1000L SIP
                    {
                        ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportSIPB.rdlc";
                        ReportViewer1.LocalReport.SetParameters(list);
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SIP_SET", dt_data));
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SipStep", dt_step));
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SIPDetail", dt_Ster));
                    }
                    if (tankid == 2)//2000L SIP
                    {
                        ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportSIPC.rdlc";
                        ReportViewer1.LocalReport.SetParameters(list);
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SIP_SET2000", dt_data));
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SipStep", dt_step));
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SIPDetail2000", dt_Ster));
                    }
                    if (tankid == 3)//灌封线 SIP
                    {
                        ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportSIPD.rdlc";
                        ReportViewer1.LocalReport.SetParameters(list);
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SIP_SETLine", dt_data));
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SipStep", dt_step));
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SIPDetailLine", dt_Ster));
                    }

                    ReportViewer1.RefreshReport();
                    return;
                }
                if (tankid > 3 && tankid < 6)//浓配SIP 4,5
                {
                    //获取步骤信息
                    DataTable dtstep = new DataTable();
                    string sql = "(SELECT * FROM (SELECT PROCESSTYPE,stepno, logtime as StartTime,lead(logtime,1)over (order by logtime) as StopTime from tblStepInfo where reportname='" + batchNameBase.reportname + "') T WHERE T.processtype=1) " +
                        "UNION  (SELECT DISTINCT T.PROCESSTYPE,STEPNO=-1,MIN(T.STARTTIME) StartTime,MAX(T.STOPTIME) AS StopTime FROM (SELECT PROCESSTYPE,stepno, logtime as StartTime,lead(logtime,1)over (order by logtime) as StopTime from tblStepInfo where reportname='" + batchNameBase.reportname + "') T where T.PROCESSTYPE >1 AND T.PROCESSTYPE<6 GROUP BY T.PROCESSTYPE  )";
                    dtstep = SQLHelper.ExecuteDataTable(sql);
                    //固定一秒钟的数据,找出灭菌开始和结束的索引值


                    string sipStart = dtstep.Compute("MIN(starttime)", "true").ToString();
                    string sipStop = dtstep.Compute("MAX(stoptime)", "true").ToString();

                    //获取灭菌步的开始和结束时间，判断时间间隔是否达标
                    string sterStart = dtstep.Compute("min(starttime)", "processtype=1 and stepno=5").ToString();
                    string sterStop = dtstep.Compute("max(stoptime)", "processtype=1 and stepno=5").ToString();




                    //计算开始和结束的时间间隔 需要考虑只有吹扫步骤的模式
                    if (sterStart == "" || sterStop == "")
                    {
                        //MessageBox.Show("数据查询完成");
                        ToolStripStatusLabel3.Text = "数据查询完成";
                        string[] startdate = sipStart.Split(' ');

                        //往报表里面传递参数        
                        list.Add(new ReportParameter("BatchString", batchNameBase.reportname));
                        list.Add(new ReportParameter("ReportType", "SIP"));

                        list.Add(new ReportParameter("Operator", batchNameBase.username));//操作名称
                        list.Add(new ReportParameter("Date", Convert.ToDateTime(startdate[0]).ToString("yyyy-MM-dd")));//灭菌日期

                        list.Add(new ReportParameter("StartTime", Convert.ToDateTime(sipStart).ToString("yyyy-MM-dd HH:mm:ss")));//SIP开始时间
                        list.Add(new ReportParameter("StopTime", Convert.ToDateTime(sipStop).ToString("yyyy-MM-dd HH:mm:ss")));//SIP结束时间


                        ReportViewer1.Visible = true;

                        ReportViewer1.ProcessingMode = ProcessingMode.Local;
                        ReportViewer1.LocalReport.DataSources.Clear();
                        ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportSIPBWithoutSIP.rdlc";
                        ReportViewer1.LocalReport.SetParameters(list);

                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SipStep", dtstep));

                        ReportViewer1.RefreshReport();

                    }
                    else
                    {

                        int tsSP = (int)batchNameBase.timesp;
                        int ts = (int)Convert.ToDateTime(sterStop).Subtract(Convert.ToDateTime(sterStart)).TotalSeconds;

                        if (ts != tsSP)
                        {
                            string rightStart = (Convert.ToDateTime(sterStop).AddSeconds(-1 * tsSP)).ToString("yyyy-MM-dd HH:mm:ss");
                            string sqlA = "exec usp_testF '" + rightStart + "'" + ",'" + sterStart + "','" + batchNameBase.reportname + "'";
                            SQLHelper.ExecuteNonQuery(sqlA);
                            Thread.Sleep(500);
                            //调整后，需要更新步骤信息
                            dtstep = SQLHelper.ExecuteDataTable(sql);
                            //更新灭菌步骤的开始和停止时间
                            sterStart = dtstep.Compute("min(starttime)", "processtype=1 and stepno=5").ToString();
                            sterStop = dtstep.Compute("max(stoptime)", "processtype=1 and stepno=5").ToString();

                        }
                        
                        //dtstep = SIPTools.GetSIPStepInfos(Source, batchNameBase.reportname);
                        ////步序时间调整
                        //if (XmlHelper.ReadXml("switchbit", "StepCheck") == "1")
                        //{
                        //    SIPTools.CalSIPStep(dtstep);
                        //    dtstep = SIPTools.GetSIPStepInfos(Source, batchNameBase.reportname);
                        //}

                        DataTable dt_Ster = new DataTable();
                        DataTable dt_data = new DataTable();
                        //获取整个灭菌过程的数据
                        if (tankid == 4)
                        {
                            //趋势图数据 1S固定间隔
                            dt_data = Get630LTTData(loc2utcstr(sipStart), loc2utcstr((Convert.ToDateTime(sipStop).AddSeconds(1)).ToString()), "1");
                            //获取灭菌段的详细数据
                            dt_Ster = Get630LTTData(loc2utcstr(sterStart), loc2utcstr((Convert.ToDateTime(sterStop).AddSeconds(1)).ToString()), SampleTime);
                        }
                        if (tankid == 5)
                        {
                            //趋势图数据 1S固定间隔
                            dt_data = Get20LTTData(loc2utcstr(sipStart), loc2utcstr((Convert.ToDateTime(sipStop).AddSeconds(1)).ToString()), "1");
                            //获取灭菌段的详细数据
                            dt_Ster = Get20LTTData(loc2utcstr(sterStart), loc2utcstr((Convert.ToDateTime(sterStop).AddSeconds(1)).ToString()), SampleTime);
                        }
                        //这两个索引值是用来给趋势图做划线用的，趋势图里面的两条竖线
                        int startIndex = (int)Convert.ToDateTime(sterStart).Subtract(Convert.ToDateTime(sipStart)).TotalSeconds - 2;
                        int stopIndex = startIndex + (int)((BatchNameBases)cmb_Batch.SelectedItem).timesp;


                        //MessageBox.Show("数据查询完成");
                        ToolStripStatusLabel3.Text = "数据查询完成";
                        string[] startdate = sipStart.Split(' ');

                        //往报表里面传递参数        
                        list.Add(new ReportParameter("BatchString", batchNameBase.reportname));
                        list.Add(new ReportParameter("ReportType", "SIP"));
                        list.Add(new ReportParameter("Par0", startIndex.ToString()));//灭菌开始的索引
                        list.Add(new ReportParameter("Par1", stopIndex.ToString()));//灭菌结束的索引
                        list.Add(new ReportParameter("Par2", Convert.ToDateTime(sterStart).ToString("yyyy-MM-dd HH:mm:ss")));//灭菌开始的时间
                        list.Add(new ReportParameter("Par3", Convert.ToDateTime(sterStop).ToString("yyyy-MM-dd HH:mm:ss")));//灭菌结束的时间
                        list.Add(new ReportParameter("Temp", batchNameBase.tempsp.ToString()));//灭菌温度
                        list.Add(new ReportParameter("PT", batchNameBase.ptsp.ToString()));//灭菌压力
                        list.Add(new ReportParameter("time", batchNameBase.timesp.ToString()));//灭菌时间

                        list.Add(new ReportParameter("Operator", batchNameBase.username));//操作名称
                        list.Add(new ReportParameter("Date", Convert.ToDateTime(startdate[0]).ToString("yyyy-MM-dd")));//灭菌日期
                        list.Add(new ReportParameter("Pingming", "AAA"));//品名
                        list.Add(new ReportParameter("Guige", "500L"));//规格
                        list.Add(new ReportParameter("StartTime", Convert.ToDateTime(sipStart).ToString("yyyy-MM-dd HH:mm:ss")));//SIP开始时间
                        list.Add(new ReportParameter("StopTime", Convert.ToDateTime(sipStop).ToString("yyyy-MM-dd HH:mm:ss")));//SIP结束时间
                        list.Add(new ReportParameter("DetailDisplay", (this.checkBox1.Checked == true) ? "true" : "false"));

                        ReportViewer1.Visible = true;

                        ReportViewer1.ProcessingMode = ProcessingMode.Local;
                        ReportViewer1.LocalReport.DataSources.Clear();


                        //ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SIP_SET", dt_data));
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SipStep", dtstep));
                        if (tankid == 4)
                        {
                            ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportSIPB630.rdlc";
                            ReportViewer1.LocalReport.SetParameters(list);
                            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("dataSter", dt_Ster));
                            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("dataTrend", dt_data));
                        }
                        if (tankid == 5)
                        {
                            ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportSIPB20.rdlc";
                            ReportViewer1.LocalReport.SetParameters(list);
                            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("dataSter20", dt_Ster));
                            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("dataTrend20", dt_data));
                        }

                        ReportViewer1.RefreshReport();
                        return;
                    }
                }
            }
            if (reportType == 2)
            {
                //稀配SIP CIP数据不一致
                //针对稀配CIP
                if (cmb_Batch.SelectedItem is BatchNameBasesCIP)
                {
                    BatchNameBasesCIP batchNameBaseCIP = (BatchNameBasesCIP)cmb_Batch.SelectedItem;
                    tankid = (int)batchNameBaseCIP.tankid;//获取罐体编号  1=1000L  2=2000L 3=稀配 Line  4 630L  5 浓配 LINE
                                                          //获取CIP步骤信息
                    DataTable dt_step = new DataTable();
                    dt_step = SIPTools.GetCIPStepInfos(Source, batchNameBaseCIP.reportname);

                    if (XmlHelper.ReadXml("switchbit", "StepCheck") =="1")
                    {
                        //步序时间的调整
                        SIPTools.CalCIPStep(dt_step);
                        dt_step = SIPTools.GetCIPStepInfos(Source, batchNameBaseCIP.reportname);
                    }
                    //CIP开始和结束时间
                    string cipStart = dt_step.Compute("MIN(StartTime)", "true").ToString();
                    string cipStop = dt_step.Compute("MAX(StopTime)", "true").ToString();

                    DataTable cipData = new DataTable();
                    cipData = SQLHelper.ExecuteDataTable(SIPTools.GetCmdTextString(Source, SampleTime, batchNameBaseCIP.reportname, cipStart, cipStop));//SQLHelper.ExecuteDataTable(SIPTools.GetCmdTextString(Source, SampleTime, batchNameBase.reportname));

                    //查询出电导率检测步的开始时间和结束时间以及采集的数据总数
                    DataRow[] array = cipData.Select("batchstep=4");
                    //把DataRow的数据转成DataTable
                    DataTable dtRinse = array.ToDataTable();//扩展方法


                    //MessageBox.Show("数据查询完成");
                    ToolStripStatusLabel3.Text = "数据查询完成";
                    string[] startdate = cipStart.Split(' ');

                    //往报表里面传递参数

                    list.Add(new ReportParameter("BatchString", batchNameBaseCIP.reportname));
                    list.Add(new ReportParameter("ReportType", "CIP"));


                    list.Add(new ReportParameter("Operator", batchNameBaseCIP.username));//操作名称
                    list.Add(new ReportParameter("Date", Convert.ToDateTime(startdate[0]).ToString("yyyy-MM-dd")));//灭菌日期
                    list.Add(new ReportParameter("StartTime", Convert.ToDateTime(cipStart).ToString("yyyy-MM-dd HH:mm:ss")));//CIP开始时间
                    list.Add(new ReportParameter("StopTime", Convert.ToDateTime(cipStop).ToString("yyyy-MM-dd HH:mm:ss")));//CIP结束时间
                    ReportViewer1.Visible = true;

                    ReportViewer1.ProcessingMode = ProcessingMode.Local;
                    ReportViewer1.LocalReport.DataSources.Clear();

                    if (array.Length > 0)//有电导率检查步骤
                    {
                        if (tankid == 1)
                        {
                            bool CT1002_MAX = Convert.ToDouble(cipData.Compute("MAX(CT1002)", "TRUE")) > 1.0;
                            bool CT2001_MAX = Convert.ToDouble(cipData.Compute("MAX(CT2001)", "TRUE")) > 1.0;


                            list.Add(new ReportParameter("CTResult", (CT1002_MAX || CT2001_MAX) ? "true" : "false"));
                            ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportCIPA.rdlc";
                            ReportViewer1.LocalReport.SetParameters(list);
                            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SipStep", dt_step));
                            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("CIPDetail", dtRinse));
                        }
                        if (tankid == 2)
                        {
                            bool CT1002_MAX = Convert.ToDouble(cipData.Compute("MAX(CT1002)", "TRUE")) > 1.0;
                            bool CT2101_MAX = Convert.ToDouble(cipData.Compute("MAX(CT2101)", "TRUE")) > 1.0;

                            list.Add(new ReportParameter("CTResult", (CT1002_MAX || CT2101_MAX) ? "true" : "false"));
                            ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportCIPB.rdlc";
                            ReportViewer1.LocalReport.SetParameters(list);
                            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SipStep", dt_step));
                            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("CIPDetail", dtRinse));
                        }

                    }
                    else//没有电导率检测步骤的报表
                    {
                        list.Add(new ReportParameter("tankId", tankid.ToString()));//操作名称
                        ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportCIPC.rdlc";
                        ReportViewer1.LocalReport.SetParameters(list);
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SipStep", dt_step));
                    }


                    ReportViewer1.RefreshReport();
                    return;

                }

                //浓配CIP
                else
                {
                    BatchNameBases batchNameBaseCIPNP = (BatchNameBases)cmb_Batch.SelectedItem;
                    tankid = (int)batchNameBaseCIPNP.tankid;//获取罐体编号  1=1000L  2=2000L 3=稀配 Line  4 630L  5 浓配 LINE

                    DataTable dtstep = new DataTable();
                    string sql = "SELECT processtype,MIN(starttime) as starttime,max(stoptime) as stoptime from(SELECT PROCESSTYPE, stepno, logtime as StartTime, lead(logtime, 1)over(order by logtime) as StopTime from tblStepInfo where reportname = '" + batchNameBaseCIPNP.reportname + "') t where t.stepno < 101 group by t.processtype";
                    dtstep = SQLHelper.ExecuteDataTable(sql);

                    //CIP开始和结束时间
                    string cipStart = dtstep.Compute("MIN(starttime)", "true").ToString();
                    
                    string cipStop = dtstep.Compute("MAX(stoptime)", "true").ToString();

                    string sampleTime = cmb_SampleTime.SelectedItem.ToString();

                    //电导检测时间
                    string ctstart = dtstep.Compute("MIN(starttime)", "processtype=4").ToString();
                    string ctstop = dtstep.Compute("MAX(stoptime)", "processtype=4").ToString();
                    string[] strTime = cipStart.Split(' ');

                    //往报表里面传递参数
                    list.Add(new ReportParameter("BatchString", batchNameBaseCIPNP.reportname));
                    list.Add(new ReportParameter("ReportType", "CIP"));
                    list.Add(new ReportParameter("Operator", batchNameBaseCIPNP.username));//操作名称
                    list.Add(new ReportParameter("Date", Convert.ToDateTime(strTime[0]).ToString("yyyy-MM-dd")));//灭菌日期
                    //list.Add(new ReportParameter("Date",Convert.ToDateTime( cipStart.Substring(0, 10)).ToString("yyyy-MM-dd")));//灭菌日期
                    list.Add(new ReportParameter("StartTime", Convert.ToDateTime(cipStart).ToString("yyyy-MM-dd HH:mm:ss")));//SIP开始时间
                    list.Add(new ReportParameter("StopTime", Convert.ToDateTime(cipStop).ToString("yyyy-MM-dd HH:mm:ss")));//SIP结束时间
                    ReportViewer1.Visible = true;

                    ReportViewer1.ProcessingMode = ProcessingMode.Local;
                    ReportViewer1.LocalReport.DataSources.Clear();

                    if (ctstart == "" || ctstop == "")
                    {
                        list.Add(new ReportParameter("tankId",tankid.ToString()));
                        ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportCIPC.rdlc";
                        ReportViewer1.LocalReport.SetParameters(list);
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("SipStep", dtstep));
                    }
                    else
                    {
                        DataTable cipData = new DataTable();
                        cipData = Get630LCTData(loc2utcstr(ctstart), loc2utcstr(ctstop), "1");

                        bool CT1001_MAX = Convert.ToDouble(cipData.Compute("MAX(CT1002)", "TRUE")) > 1.0;
                        bool CT1002_MAX = Convert.ToDouble(cipData.Compute("MAX(CT1002)", "TRUE")) > 1.0;
                        bool CT2001_MAX = Convert.ToDouble(cipData.Compute("MAX(CT2001)", "TRUE")) > 1.0;
                        list.Add(new ReportParameter("CTResult", (CT1001_MAX || CT1002_MAX || CT2001_MAX) ? "true" : "false"));
                        ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportCIPB630.rdlc";
                        ReportViewer1.LocalReport.SetParameters(list);
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("CipStep", dtstep));
                        ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("CIPData", cipData));

                    }
                    //MessageBox.Show("数据查询完成");
                    ToolStripStatusLabel3.Text = "数据查询完成";
                    ReportViewer1.RefreshReport();
                }
            }


            if (reportType == 3)
            {
                BatchNameBasesProd batchNameBaseProd = (BatchNameBasesProd)cmb_Batch.SelectedItem;
                tankid = (int)batchNameBaseProd.tankid;
                if (tankid == 1 || tankid == 2)//稀配罐
                {
                    //连续两步会导致上一步没有结束时间
                   // SIPTools.ProductionPhaseTimeCal(((BatchNameBasesProd)cmb_Batch.SelectedItem).reportname);
                                     
                    DataTable dtPro = SIPTools.GetProductionInfo(((BatchNameBasesProd)cmb_Batch.SelectedItem).reportname);
                   
                    list.Add(new ReportParameter("BatchString", ((BatchNameBasesProd)cmb_Batch.SelectedItem).reportname));
                    list.Add(new ReportParameter("tankId", tankid.ToString()));
                    list.Add(new ReportParameter("RecipeName", ((BatchNameBasesProd)cmb_Batch.SelectedItem).recipename));
                    list.Add(new ReportParameter("Operator", ((BatchNameBasesProd)cmb_Batch.SelectedItem).username));
                    list.Add(new ReportParameter("Date", Convert.ToDateTime(((BatchNameBasesProd)cmb_Batch.SelectedItem).starttime.ToString().Split(' ')[0]).ToString("yyyy-MM-dd")));
                    list.Add(new ReportParameter("StartTime", Convert.ToDateTime(((BatchNameBasesProd)cmb_Batch.SelectedItem).starttime.ToString()).ToString("yyyy-MM-dd HH:mm:ss")));
                    list.Add(new ReportParameter("StopTime", Convert.ToDateTime(((BatchNameBasesProd)cmb_Batch.SelectedItem).stoptime.ToString()).ToString("yyyy-MM-dd HH:mm:ss")));

                    ReportViewer1.Visible = true;
                    ReportViewer1.ProcessingMode = ProcessingMode.Local;
                    ReportViewer1.LocalReport.DataSources.Clear();

                    ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportPro.rdlc";
                    ReportViewer1.LocalReport.SetParameters(list);
                    ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("ProData", dtPro));
                  
                    ReportViewer1.RefreshReport();
                    //MessageBox.Show("数据查询完成");
                    ToolStripStatusLabel3.Text = "数据查询完成";
                    return;

                }
                if (tankid == 4)//浓配罐
                {
                    //连续两步会导致上一步没有结束时间
                   // SIPTools.ProductionPhaseTimeCal(((BatchNameBasesProd)cmb_Batch.SelectedItem).reportname);
                   

                    DataTable dtPro = SIPTools.GetProductionInfo(((BatchNameBasesProd)cmb_Batch.SelectedItem).reportname);
                   // string sqlb = "SELECT [logtime],[WT],[TT],[PT] FROM [ReportDB].[dbo].[tblProDataLog] WHERE reportname='" + ((BatchNameBasesProd)cmb_Batch.SelectedItem).reportname + "'";
                   // DataTable dtQushi = SQLHelper.ExecuteDataTable(sqlb);

                    list.Add(new ReportParameter("BatchString", ((BatchNameBasesProd)cmb_Batch.SelectedItem).reportname));
                    list.Add(new ReportParameter("tankId", tankid.ToString()));
                    list.Add(new ReportParameter("RecipeName", ((BatchNameBasesProd)cmb_Batch.SelectedItem).recipename));
                    list.Add(new ReportParameter("Operator", ((BatchNameBasesProd)cmb_Batch.SelectedItem).username));
                    list.Add(new ReportParameter("Date", Convert.ToDateTime(((BatchNameBasesProd)cmb_Batch.SelectedItem).starttime.ToString().Split(' ')[0]).ToString("yyyy-MM-dd")));
                    list.Add(new ReportParameter("StartTime", Convert.ToDateTime(((BatchNameBasesProd)cmb_Batch.SelectedItem).starttime.ToString()).ToString("yyyy-MM-dd HH:mm:ss")));
                    list.Add(new ReportParameter("StopTime", Convert.ToDateTime(((BatchNameBasesProd)cmb_Batch.SelectedItem).stoptime.ToString()).ToString("yyyy-MM-dd HH:mm:ss")));

                    ReportViewer1.Visible = true;
                    ReportViewer1.ProcessingMode = ProcessingMode.Local;
                    ReportViewer1.LocalReport.DataSources.Clear();

                    ReportViewer1.LocalReport.ReportPath = this.appPath + "\\ReportPro.rdlc";
                    ReportViewer1.LocalReport.SetParameters(list);
                    ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("ProData", dtPro));
                    ReportViewer1.RefreshReport();
                    ToolStripStatusLabel3.Text = "数据查询完成";
                    return;
                }
            }
        }


        private void cmb_ReportType_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmb_ReportType.SelectedItem.ToString() == "灭菌")
            {
                this.checkBox1.Visible = true;
            }
            else
            {
                this.checkBox1.Visible = false;
                this.checkBox1.Checked = false;
            }
        }
        private string loc2utcstr(string strtime)
        {
            return System.DateTime.Parse(strtime).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss");
        }

        private DataTable Get2000LTTData(string tfrom, string tto, string sampletime)
        {
            string timeStep = ",'TIMESTEP=" + sampletime + ",1'";
            string sql = "";
            DataTable dtReturn = new DataTable();
            DataColumn newColumn = new DataColumn("Timestamp", System.Type.GetType("System.String"));
            newColumn.Caption = "Timestamp";
            newColumn.DefaultValue = string.Empty;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT3106", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT3106";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);


            newColumn = new DataColumn("TT3004", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT3004";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT2101", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2101";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT2102", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2102";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT2150", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2150";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);
            //*************************************************************************************************************
            //TT3106
            sql = "TAG:R,98,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt3106 = GetArchiveTable(sql, "TT3106");

            sql = "TAG:R,92,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt3004 = GetArchiveTable(sql, "TT3004");
            sql = "TAG:R,104,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt2101 = GetArchiveTable(sql, "TT2101");
            sql = "TAG:R,107,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt2102 = GetArchiveTable(sql, "TT2102");
            sql = "TAG:R,88,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt2150 = GetArchiveTable(sql, "TT2150");

            var query1 = from tt3106 in dt3106.AsEnumerable()
                         join tt3004 in dt3004.AsEnumerable()
                         on tt3106.Field<DateTime>("Timestamp") equals tt3004.Field<DateTime>("Timestamp") into dt0604

                         from tt0604 in dt0604.DefaultIfEmpty()
                         join tt2101 in dt2101.AsEnumerable()
                         on tt3106.Field<DateTime>("Timestamp") equals tt2101.Field<DateTime>("Timestamp") into dt060401

                         from tt060401 in dt060401.DefaultIfEmpty()
                         join tt2102 in dt2102.AsEnumerable()
                         on tt3106.Field<DateTime>("Timestamp") equals tt2102.Field<DateTime>("Timestamp") into dt06040102

                         from tt06040102 in dt06040102.DefaultIfEmpty()
                         join tt2150 in dt2150.AsEnumerable()
                         on tt3106.Field<DateTime>("Timestamp") equals tt2150.Field<DateTime>("Timestamp") into dt0604010250

                         from tt0604010250 in dt0604010250.DefaultIfEmpty()

                         select new
                         {
                             Timestamp = tt3106.Field<DateTime>("Timestamp"),
                             TT3106 = tt3106.Field<double>("TT3106"),
                             TT3004 = tt0604 == null ? 0.0 : tt0604.Field<double>("TT3004"),
                             TT2101 = tt060401 == null ? 0.0 : tt060401.Field<double>("TT2101"),
                             TT2102 = tt06040102 == null ? 0.0 : tt06040102.Field<double>("TT2102"),
                             TT2150 = tt0604010250 == null ? 0.0 : tt0604010250.Field<double>("TT2150")

                         };

            dtReturn.Clear();
            foreach (var obj in query1)
            {
                dtReturn.Rows.Add (System.DateTime.Parse(obj.Timestamp.ToString()).ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss"), obj.TT3106, obj.TT3004, obj.TT2101, obj.TT2102, obj.TT2150);
            }
            dtReturn.Columns["Timestamp"].ColumnName = "logtime";
            return dtReturn;
        }

        private DataTable GetArchiveTable(string sql, string colName)
        {
            DataTable dt = OleDbHelper.ExecuteDataTable(sql);
            dt.Columns["Timestamp"].ColumnName = "logtime";
            dt.Columns["RealValue"].ColumnName = colName;
            return dt.DefaultView.ToTable(false, new string[] { "logtime", colName });
        }

        private DataTable Get630LTTData(string tfrom, string tto, string sampletime)
        {
            string timeStep = ",'TIMESTEP=" + sampletime + ",1'";
            string sql = "";
            DataTable dtReturn = new DataTable();
            DataColumn newColumn = new DataColumn("logtime", System.Type.GetType("System.String"));
            newColumn.Caption = "logtime";
            newColumn.DefaultValue = string.Empty;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT1001", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT1001";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);


            newColumn = new DataColumn("TT1002", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT1002";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);


            newColumn = new DataColumn("TT1007", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT1007";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);



            //*************************************************************************************************************
            //TT1001
            sql = "TAG:R,106,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt1001 = GetArchiveTable(sql, "TT1001");

            sql = "TAG:R,95,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt1002 = GetArchiveTable(sql, "TT1002");



            sql = "TAG:R,93,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt1007 = GetArchiveTable(sql, "TT1007");


            var query1 = from tt1001 in dt1001.AsEnumerable()
                         join tt1002 in dt1002.AsEnumerable()
                         on tt1001.Field<DateTime>("logtime") equals tt1002.Field<DateTime>("logtime") into dt0102


                         from tt0102 in dt0102.DefaultIfEmpty()
                         join tt1007 in dt1007.AsEnumerable()
                         on tt1001.Field<DateTime>("logtime") equals tt1007.Field<DateTime>("logtime") into dt010207


                         from tt010207 in dt010207.DefaultIfEmpty()

                         select new
                         {
                             logtime = tt1001.Field<DateTime>("logtime"),
                             TT1001 = tt1001.Field<double>("tt1001"),
                             TT1002 = tt0102 == null ? 0.0 : tt0102.Field<double>("TT1002"),
                             TT1007 = tt010207 == null ? 0.0 : tt010207.Field<double>("TT1007"),
                         };

            dtReturn.Clear();
            foreach (var obj in query1)
            {
                dtReturn.Rows.Add(System.DateTime.Parse(obj.logtime.ToString()).ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss"), obj.TT1001, obj.TT1002, obj.TT1007);
            }

            return dtReturn;
        }

        private DataTable Get20LTTData(string tfrom, string tto, string sampletime)
        {
            string timeStep = ",'TIMESTEP=" + sampletime + ",1'";
            string sql = "";
            DataTable dtReturn = new DataTable();
            DataColumn newColumn = new DataColumn("logtime", System.Type.GetType("System.String"));
            newColumn.Caption = "logtime";
            newColumn.DefaultValue = string.Empty;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT2002", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2002";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);


            newColumn = new DataColumn("TT1003", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT1003";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT1004", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT1004";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT1005", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT1005";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT1006", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT1006";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT3103", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT3103";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT3104", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT3104";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);
            //*************************************************************************************************************
            //TT1007
            sql = "TAG:R,96,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt2002 = GetArchiveTable(sql, "TT2002");
            //TT1003
            sql = "TAG:R,94,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt1003 = GetArchiveTable(sql, "TT1003");
            //TT1004
            sql = "TAG:R,86,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt1004 = GetArchiveTable(sql, "TT1004");
            //TT1005
            sql = "TAG:R,85,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt1005 = GetArchiveTable(sql, "TT1005");
            //TT1006
            sql = "TAG:R,101,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt1006 = GetArchiveTable(sql, "TT1006");

            //TT3103
            sql = "TAG:R,90,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt3103 = GetArchiveTable(sql, "TT3103");

            //TT3104
            sql = "TAG:R,100,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt3104 = GetArchiveTable(sql, "TT3104");

            var query1 = from tt2002 in dt2002.AsEnumerable()
                         join tt1003 in dt1003.AsEnumerable()
                         on tt2002.Field<DateTime>("logtime") equals tt1003.Field<DateTime>("logtime") into dt0703

                         from tt0703 in dt0703.DefaultIfEmpty()
                         join tt1004 in dt1004.AsEnumerable()
                         on tt2002.Field<DateTime>("logtime") equals tt1004.Field<DateTime>("logtime") into dt070304

                         from tt070304 in dt070304.DefaultIfEmpty()
                         join tt1005 in dt1005.AsEnumerable()
                         on tt2002.Field<DateTime>("logtime") equals tt1005.Field<DateTime>("logtime") into dt07030405

                         from tt07030405 in dt07030405.DefaultIfEmpty()
                         join tt1006 in dt1006.AsEnumerable()
                         on tt2002.Field<DateTime>("logtime") equals tt1006.Field<DateTime>("logtime") into dt0703040506


                         from tt0703040506 in dt0703040506.DefaultIfEmpty()
                         join tt3103 in dt3103.AsEnumerable()
                         on tt2002.Field<DateTime>("logtime") equals tt3103.Field<DateTime>("logtime") into dt070304050603

                         from tt070304050603 in dt070304050603.DefaultIfEmpty()
                         join tt3104 in dt3104.AsEnumerable()
                         on tt2002.Field<DateTime>("logtime") equals tt3104.Field<DateTime>("logtime") into dt07030405060304


                         from tt07030405060304 in dt07030405060304.DefaultIfEmpty()

                         select new
                         {
                             logtime = tt2002.Field<DateTime>("logtime"),
                             TT2002 = tt2002.Field<double>("TT2002"),
                             TT1003 = tt0703 == null ? 0.0 : tt0703.Field<double>("TT1003"),
                             TT1004 = tt070304 == null ? 0.0 : tt070304.Field<double>("TT1004"),
                             TT1005 = tt07030405 == null ? 0.0 : tt07030405.Field<double>("TT1005"),
                             TT1006 = tt0703040506 == null ? 0.0 : tt0703040506.Field<double>("TT1006"),
                             TT3103 = tt070304050603 == null ? 0.0 : tt070304050603.Field<double>("TT3103"),
                             TT3104 = tt07030405060304 == null ? 0.0 : tt07030405060304.Field<double>("TT3104")


                         };

            dtReturn.Clear();
            foreach (var obj in query1)
            {
                dtReturn.Rows.Add(System.DateTime.Parse(obj.logtime.ToString()).ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss"), obj.TT2002, obj.TT1003, obj.TT1004, obj.TT1005, obj.TT1006, obj.TT3103, obj.TT3104);
            }
          
            return dtReturn;
        }

        private DataTable Get630LCTData(string tfrom, string tto, string sampletime)
        {
            string timeStep = ",'TIMESTEP=" + sampletime + ",1'";
            string sql = "";
            DataTable dtReturn = new DataTable();
            DataColumn newColumn = new DataColumn("logtime", System.Type.GetType("System.String"));
            newColumn.Caption = "logtime";
            newColumn.DefaultValue = string.Empty;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("CT1001", System.Type.GetType("System.Double"));
            newColumn.Caption = "CT1001";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);


            newColumn = new DataColumn("CT1002", System.Type.GetType("System.Double"));
            newColumn.Caption = "CT1002";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);



            newColumn = new DataColumn("CT2001", System.Type.GetType("System.Double"));
            newColumn.Caption = "CT2001";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            //*************************************************************************************************************
            //CT1001
            sql = "TAG:R,112,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt1001 = GetArchiveTable(sql, "CT1001");
            //CT1002
            sql = "TAG:R,113,'" + tfrom + "','" + tto + "'" + timeStep;

            DataTable dt1002 = GetArchiveTable(sql, "CT1002");
            //CT2001
            sql = "TAG:R,115,'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt2001 = GetArchiveTable(sql, "CT2001");


            var query1 = from ct1001 in dt1001.AsEnumerable()
                         join ct1002 in dt1002.AsEnumerable()
                         on ct1001.Field<DateTime>("logtime") equals ct1002.Field<DateTime>("logtime") into dt0102

                         from ct0102 in dt0102.DefaultIfEmpty()
                         join ct2001 in dt2001.AsEnumerable()
                         on ct1001.Field<DateTime>("logtime") equals ct2001.Field<DateTime>("logtime") into dt010201


                         from ct010201 in dt010201.DefaultIfEmpty()

                         select new
                         {
                             logtime = ct1001.Field<DateTime>("logtime"),
                             CT1001 = ct1001.Field<double>("CT1001"),
                             CT1002 = ct0102 == null ? 0.0 : ct0102.Field<double>("CT1002"),
                             CT2001 = ct010201 == null ? 0.0 : ct010201.Field<double>("CT2001"),
                         };

            dtReturn.Clear();
            foreach (var obj in query1)
            {
                dtReturn.Rows.Add(System.DateTime.Parse(obj.logtime.ToString()).ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss"), obj.CT1001, obj.CT1002, obj.CT2001);
            }

            return dtReturn;
        }
    }
}
